#!/usr/bin/env python
#
# Calculates job-related statistics from sqlite3 simulator/job dump output
# files. The output of this script is also an sqlite3 database.
#
# Author:   Mike Murphy <mamurph@cs.clemson.edu>
# Revision: 27 September 2009
#
#   Copyright 2009 Clemson University
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.
#
# This material is based upon work supported under a National Science
# Foundation Graduate Research Fellowship. Any opinions, findings,
# conclusions or recommendations expressed in this publication are those of
# the author(s) and do not necessarily reflect the views of the National
# Science Foundation.

import numpy as np
import sqlite3, sys

if len(sys.argv) != 3:
   print >> sys.stderr, 'Usage:', sys.argv[0], '<sq3_file_in> <sq3_file_out>'
   sys.exit(2)
#

print 'Extracting job statistics from', sys.argv[1]

def vo_record(vo_list, init_val=0):
   result = {}
   for vo in vo_list:
      result[vo] = init_val
   return result
#

def insert_stat(cursor, measure, value):
   cursor.execute('INSERT INTO "Stats" ("measure", "value") VALUES (?, ?);', \
      (measure, value))
#

def compute_stats(cursor, prefix, qtyname, qty):
   insert_stat(cursor, prefix + 'mean_' + qtyname, np.mean(qty))
   insert_stat(cursor, prefix + 'stddev_' + qtyname, np.std(qty, ddof=1))
   insert_stat(cursor, prefix + 'min_' + qtyname, min(qty))
   insert_stat(cursor, prefix + 'max_' + qtyname, max(qty))
   insert_stat(cursor, prefix + 'median_' + qtyname, np.median(qty))
#

in_conn = sqlite3.connect(sys.argv[1])
in_conn.row_factory = sqlite3.Row
out_conn = sqlite3.connect(sys.argv[2])

in_cursor = in_conn.cursor()
out_cursor = out_conn.cursor()

out_cursor.execute('CREATE TABLE "Stats" ("measure", "value");')
out_cursor.execute('CREATE TABLE "Aggregate" ("arrival", "arrival_delta", ' + \
   '"service", "sojourn", "wait");')
out_cursor.execute('CREATE TABLE "Rates" ("time", "rate");')

vo_list = []
in_cursor.execute('SELECT DISTINCT "name" FROM "VO";')
for row in in_cursor:
   vo = row['name']
   name = '"VO_' + vo + '"'
   out_cursor.execute('CREATE TABLE ' + name + \
      ' ("arrival", "arrival_delta", "service", "sojourn", "wait");')
   name = '"Rates_' + vo + '"'
   out_cursor.execute('CREATE TABLE ' + name + ' ("time", "rate");')
   vo_list.append(vo)
#

out_conn.commit()

last_arrive = 0
arrival_count = 0
last_arrival_time = -1
arrival_deltas = []
service_times = []
sojourn_times = []
wait_times = []
arrival_rates = []
vo_last_arrive = vo_record(vo_list)
vo_arrival_count = vo_record(vo_list)
vo_last_arrival_time = vo_record(vo_list, -1)
vo_arrival_deltas = vo_record(vo_list, [])
vo_service_times = vo_record(vo_list, [])
vo_sojourn_times = vo_record(vo_list, [])
vo_wait_times = vo_record(vo_list, [])
vo_arrival_rates = vo_record(vo_list, [])

vo_string = '"' + '","'.join(vo_list) + '"'
query = 'SELECT * FROM "Job" WHERE "vo" IN (' + vo_string + \
        ') ORDER BY "submit_time" ASC;'
in_cursor.execute(query)
for row in in_cursor:
   vo = row['vo']
   arrive = row['submit_time']
   start = row['start_time']
   finish = row['finish_time']
   service = finish - start
   sojourn = finish - arrive
   wait = start - arrive
   d_all_arrive = arrive - last_arrive
   d_vo_arrive = arrive - vo_last_arrive[vo]
   out_cursor.execute('INSERT INTO "Aggregate" ("arrival", "arrival_delta", '+\
      '"service", "sojourn", "wait") VALUES (?, ?, ?, ?, ?);', (arrive, \
      d_all_arrive, service, sojourn, wait))
   out_cursor.execute('INSERT INTO "VO_' + vo + '" ("arrival", ' + \
      '"arrival_delta", "service", "sojourn", "wait") VALUES ' + \
      '(?, ?, ?, ?, ?);', (arrive, d_vo_arrive, service, sojourn, wait))
   #
   arrival_deltas.append(d_all_arrive)
   service_times.append(service)
   sojourn_times.append(sojourn)
   wait_times.append(wait)
   vo_arrival_deltas[vo].append(d_vo_arrive)
   vo_service_times[vo].append(service)
   vo_sojourn_times[vo].append(sojourn)
   vo_wait_times[vo].append(wait)
   last_arrive = arrive
   vo_last_arrive[vo] = arrive
   #
   if arrive > last_arrival_time:
      if last_arrival_time >= 0:
         out_cursor.execute(\
            'INSERT INTO "Rates" ("time", "rate") VALUES (?, ?);', \
            (last_arrival_time, arrival_count))
         arrival_rates.append(arrival_count)
      last_arrival_time = arrive
      arrival_count = 1
   else:
      arrival_count += 1
   #
   if arrive > vo_last_arrival_time[vo]:
      if vo_last_arrival_time[vo] >= 0:
         out_cursor.execute('INSERT INTO "Rates_' + vo + \
            '" ("time", "rate") VALUES (?, ?);', \
            (vo_last_arrival_time[vo], vo_arrival_count[vo]))
         vo_arrival_rates[vo].append(vo_arrival_count[vo])
      vo_last_arrival_time[vo] = arrive
      vo_arrival_count[vo] = 1
   else:
      vo_arrival_count[vo] += 1
#

# Flush final rate results
if (last_arrival_time >= 0) and (arrival_count > 0):
   out_cursor.execute('INSERT INTO "Rates" ("time", "rate") VALUES (?, ?);', \
      (last_arrival_time, arrival_count))
   arrival_rates.append(arrival_count)
for vo in vo_list:
   if (vo_last_arrival_time[vo] >= 0) and (vo_arrival_count[vo] > 0):
      out_cursor.execute('INSERT INTO "Rates_' + vo + \
         '" ("time", "rate") VALUES (?, ?);', \
         (vo_last_arrival_time[vo], vo_arrival_count[vo]))
      vo_arrival_rates[vo].append(vo_arrival_count[vo])
#

out_conn.commit()
in_cursor.close()
in_conn.close()

compute_stats(out_cursor, '', 'interarrival_time', arrival_deltas)
compute_stats(out_cursor, '', 'service_time', service_times)
compute_stats(out_cursor, '', 'sojourn_time', sojourn_times)
compute_stats(out_cursor, '', 'wait_time', wait_times)
compute_stats(out_cursor, '', 'arrival_rate', arrival_rates)

for vo in vo_list:
   prefix = vo + '_'
   compute_stats(out_cursor, prefix, 'interarrival_time', vo_arrival_deltas[vo])
   compute_stats(out_cursor, prefix, 'service_time', vo_service_times[vo])
   compute_stats(out_cursor, prefix, 'sojourn_time', vo_sojourn_times[vo])
   compute_stats(out_cursor, prefix, 'wait_time', vo_wait_times[vo])
   compute_stats(out_cursor, prefix, 'arrival_rate', vo_arrival_rates[vo])
#

out_conn.commit()
out_cursor.close()
out_conn.close()
